﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Citi.RoutineBase;
using Microsoft.Office.Interop.Excel;
using excel = Microsoft.Office.Interop.Excel;
using System.IO;
using OfficeOpenXml;
using Citi.ATM.SchedulerUI.EmailSenderWcf;
using Citi.ATM.Domain;
using Citi.Helpers;
using Citi.ATM.Domain.Sharepoint.ATM;

namespace Citi.ATM.SchedulerUI
{

    public class MonthlyReport : IExecutableRoutine
    {


        public void Rollback(ref ICurrentAction currentAction)
        {

        }

        public void Start(DateTime scheduledDate, ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {
            var chartPath = @"\\LACBRA001FS0010\data_grp1\FTOOLS\Apps\ATMAutomation\Charts\ChartTemplate.xlsx";
            var destinyPath = @"\\LACBRA001FS0010\DATA_GRP1\FTOOLS\APPS\ATMAUTOMATION\CHARTS\IMAGES\";

            RefreshChartDataSource(chartPath);
            ExportChart(chartPath, destinyPath);

            //SendEmail(GetMonthlyHtml(), string.Format("***Eventos Criticos de ATM - Relatório Consolidado Mensal {0}***", DateTime.Today.ToString("dd/MM/yyyy")), "*CTI BR ATM Services Reports <dl.cti.br.atm.services.reports@imcla.lac.nsroot.net>", "");
            SendEmail(GetMonthlyHtml(), string.Format("***Eventos Criticos de ATM - Relatório Consolidado Mensal {0}***", DateTime.Today.ToString("dd/MM/yyyy")), "*CTI BR ATM Services <LAC_BRA_FTOOLS@imcla.lac.nsroot.net>", "");
        }

        public string GetMonthlyHtml()
        {
            var template = EmailHelper.GetEmailBody(@"\\LACBRA001FS0010\DATA_GRP1\FTOOLS\Apps\ATMAutomation\HTML\MonthlyEmail.htm");
            var templateContent = EmailHelper.GetEmailBody(@"\\LACBRA001FS0010\DATA_GRP1\FTOOLS\Apps\ATMAutomation\HTML\MonthlyContent.htm");

            return template.Replace("[MonthlyContent]", templateContent);
        }

        public void Stop(ref ICurrentAction currentAction)
        {

        }

        public void RefreshChartDataSource(string chartPath)
        {
            var templateFile = new FileInfo(chartPath);

            using (var package = new ExcelPackage(templateFile))
            {
                ExcelWorkbook workbook = package.Workbook;

                if (workbook != null)
                {
                    ExcelWorksheet currentWorksheet = workbook.Worksheets.First();

                    var lastThreeMonths = DateTime.Today.AddMonths(-3);

                    var events = new ATMEvents().GetIncidentVersusBranch(new DateTime(lastThreeMonths.Year, lastThreeMonths.Month, 1));
                    // var branchs = events.GroupBy(x => x.Nome).Select(group => new { Nome = group.Key, Count = group.Count() }).OrderByDescending(x => x.Count).Take(5);
                    //var branchs = events.Select(x => x.Nome).Distinct();

                    //Refresh Headeres
                    currentWorksheet.Cells[1, 1].Value = currentWorksheet.Cells[1, 4].Value = currentWorksheet.Cells[3, 7].Value = DateTime.Today.AddMonths(-3).ToString("MMMM").ToUpper();
                    currentWorksheet.Cells[8, 1].Value = currentWorksheet.Cells[8, 4].Value = currentWorksheet.Cells[4, 7].Value = DateTime.Today.AddMonths(-2).ToString("MMMM").ToUpper();
                    currentWorksheet.Cells[15, 1].Value = currentWorksheet.Cells[15, 4].Value = currentWorksheet.Cells[5, 7].Value = DateTime.Today.AddMonths(-1).ToString("MMMM").ToUpper();

                    FillIncidentsNumber(currentWorksheet, events);
                    FillIncidentsHours(currentWorksheet, events);

                    for (int i = 2; i >= 0; i--)
                    {
                        var allEventsByMonth = events.Where(x => x.Data.HasValue && x.Data.Value.Month == DateTime.Today.AddMonths(-i).Month);
                        currentWorksheet.Cells[5 - i, 8].Value = allEventsByMonth.Count();
                    }

                }
                package.Save();

            }

        }

        private static int FillIncidentsNumber(ExcelWorksheet currentWorksheet, IEnumerable<EventosAbertosItem> events)
        {
            var row = 1;

            for (int i = 2; i >= 0; i--)
            {
                row = row + 2;
                var branchs = events.Where(x => x.Data.HasValue && x.Data.Value.Month == DateTime.Today.AddMonths(-i).Month)
                    .GroupBy(x => x.Nome)
                    .Select(group => new { Nome = group.Key, Count = group.Count() })
                    .OrderByDescending(x => x.Count).Take(5);

                foreach (var item in branchs)
                {
                    var eventsByBranchByMonth = events.Where(x => x.Data.HasValue && x.Nome == item.Nome && x.Data.Value.Month == DateTime.Today.AddMonths(-i).Month);
                    currentWorksheet.Cells[row, 1].Value = item.Nome;
                    currentWorksheet.Cells[row, 2].Value = eventsByBranchByMonth.Count();
                    //currentWorksheet.Cells[row, 5].Value = eventsByBranchByMonth.Sum(x => Convert.ToInt16(x.IndisponibilidadeMin) / 60);
                    row++;
                }
                row = row + 5 - branchs.Count();
            }

            return row;
        }

        private static int FillIncidentsHours(ExcelWorksheet currentWorksheet, IEnumerable<EventosAbertosItem> events)
        {
            var row = 1;

            for (int i = 2; i >= 0; i--)
            {
                row = row + 2;
                var branchs = events.Where(x => x.Data.HasValue && x.Data.Value.Month == DateTime.Today.AddMonths(-i).Month)
                    .GroupBy(x => x.Nome)
                    .Select(group => new { Nome = group.Key, Unavaiability = group.Sum(y => int.Parse(y.IndisponibilidadeMin)) / 60 })
                    .OrderByDescending(x => x.Unavaiability).Take(5);

                foreach (var item in branchs)
                {
                    //var eventsByBranchByMonth = events.Where(x => x.Data.HasValue && x.Nome == item.Nome && x.Data.Value.Month == DateTime.Today.AddMonths(-i).Month);
                    currentWorksheet.Cells[row, 4].Value = item.Nome;
                    currentWorksheet.Cells[row, 5].Value = item.Unavaiability;//eventsByBranchByMonth.Sum(x => Convert.ToInt16(x.IndisponibilidadeMin) / 60);
                    row++;
                }
                row = row + 5 - branchs.Count();
            }

            return row;
        }

        public void ExportChart(string chartPath, string destinySavePath)
        {
            var xlApp = new Application();
            var xlWorkBook = xlApp.Workbooks.Open(chartPath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true, 1, 0);
            var xlWorkSheet = (excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
            var misValue = System.Reflection.Missing.Value;
            var fileName = string.Empty;

            for (int i = 1; i <= 7; i++)
            {
                var charts = (excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
                var chart = (excel.ChartObject)charts.Item(i);
                var chartPage = chart.Chart;

                fileName = string.Concat("Chart", i, ".png");

                chartPage.Export(Path.Combine(destinySavePath, fileName), "PNG", true);

                System.Threading.Thread.Sleep(2000);
            }

            releaseObject(xlWorkSheet);

            if (xlWorkBook != null)
            {
                xlWorkBook.Close(true, null, null);
                xlApp.Quit();
            }
        }

        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }

        public void SendEmail(string template, string subject, string to, string cc)
        {
            using (var client = new EmailClient())
            {
                client.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

                var email = new EmailSenderWcf.Email
                {
                    BodyMessage = template,
                    Subject = subject,
                    Attachments = new List<Attachment> { 
                        new Attachment { PathFile = @"\\LACBRA001FS0010\DATA_GRP1\FTOOLS\APPS\ATMAUTOMATION\CHARTS\IMAGES\Chart1.PNG", FriendlyName = string.Empty },
                        new Attachment { PathFile = @"\\LACBRA001FS0010\DATA_GRP1\FTOOLS\APPS\ATMAUTOMATION\CHARTS\IMAGES\Chart2.PNG", FriendlyName = string.Empty },
                        new Attachment { PathFile = @"\\LACBRA001FS0010\DATA_GRP1\FTOOLS\APPS\ATMAUTOMATION\CHARTS\IMAGES\Chart3.PNG", FriendlyName = string.Empty },
                        new Attachment { PathFile = @"\\LACBRA001FS0010\DATA_GRP1\FTOOLS\APPS\ATMAUTOMATION\CHARTS\IMAGES\Chart4.PNG", FriendlyName = string.Empty },
                        new Attachment { PathFile = @"\\LACBRA001FS0010\DATA_GRP1\FTOOLS\APPS\ATMAUTOMATION\CHARTS\IMAGES\Chart5.PNG", FriendlyName = string.Empty },
                        new Attachment { PathFile = @"\\LACBRA001FS0010\DATA_GRP1\FTOOLS\APPS\ATMAUTOMATION\CHARTS\IMAGES\Chart6.PNG", FriendlyName = string.Empty },
                        new Attachment { PathFile = @"\\LACBRA001FS0010\DATA_GRP1\FTOOLS\APPS\ATMAUTOMATION\CHARTS\IMAGES\Chart7.PNG", FriendlyName = string.Empty }
                    },
                    SentOnBehalfOfName = "O&T Finance Comunica",
                    To = to.Split(';').Select(x => new EmailSenderWcf.EmailAddress { Description = x }).ToList(),
                    Cc = cc.Split(';').Select(x => new EmailSenderWcf.EmailAddress { Description = x }).ToList(),
                    ApplicationName = "ATM Automation",
                    Url = string.Empty,
                    Parameters = new Dictionary<string, string>()
                };

                client.InsertEmailInQueue(email);
            }
        }
    }
}
